package com.far.dao;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



import org.apache.log4j.Logger;

import oracle.jdbc.OracleTypes;
import utiles.AppProperties;
import utiles.ConexionPool;
import utiles.utilitarios;
import com.far.dto.BonificacionBoletaDTO;
import com.far.dto.BonificacionDTO;
import com.portal.dto.ComboDTO;
import com.portal.dto.PacienteDTO;
import com.far.vo.BonificaEncVO;

public class FarBonificacionDAO {
	private static Logger log = Logger.getLogger(FarGuiaDespachoDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	String horaActual = utilitarios.getFechaHoraActual(); //utilitarios.getHoraActual();
	public  List<BonificacionBoletaDTO> getBoletasXBonificar(String rutPac, String kinf) throws SQLException, IOException{

		Connection conn = ConexionPool.getConnectionPool();	

		List<BonificacionBoletaDTO> listRetorno = new ArrayList<BonificacionBoletaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.getBoletasXBonificar(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPac);
			stmt.setString(2, kinf);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				BonificacionBoletaDTO boletasPendDTO = new BonificacionBoletaDTO();
				boletasPendDTO.setKinf(rs.getString(1));
				boletasPendDTO.setDescripcion(rs.getString(2)); 
				boletasPendDTO.setCantCompra(rs.getString(3)); 
				boletasPendDTO.setCantBoni(rs.getString(4));
				boletasPendDTO.setReqCompra(rs.getString(5));
				boletasPendDTO.setReqBoni(rs.getString(6));
				boletasPendDTO.setFecha(rs.getString(7));
				boletasPendDTO.setKdoc(rs.getString(8));
				boletasPendDTO.setSede(rs.getString(9));
				boletasPendDTO.setLine(rs.getString(10));
				listRetorno.add(boletasPendDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error(horaActual+"Error FarBonificacionDAO.getBoletasXBonificar SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}

	public  List<PacienteDTO> getRetira(String rutPac) throws SQLException, IOException{

		Connection conn = ConexionPool.getConnectionPool();	

		List<PacienteDTO> listRetorno = new ArrayList<PacienteDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.getRetira(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPac);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				PacienteDTO pac = new PacienteDTO();
				pac.setRut(rs.getString(1));
				pac.setNombreCompleto(rs.getString(2)); 
				pac.setTelefonos(rs.getString(3)); 
				listRetorno.add(pac);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error(horaActual+"Error FarBonificacionDAO.getRetira SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}

	public  List<BonificacionBoletaDTO> setBonificaEnc(BonificaEncVO boniEnc) throws SQLException, IOException{

		Connection conn = ConexionPool.getConnectionPool();	

		List<BonificacionBoletaDTO> listRetorno = new ArrayList<BonificacionBoletaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.setBonificaEnc(?,?,?,?,?, ?,?,?,?,?, ?) }"; 	   
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, boniEnc.getIdSede());
			stmt.setString(2, boniEnc.getRutPac());
			stmt.setString(3, boniEnc.getKinf());
			stmt.setString(4, boniEnc.getKusr());
			stmt.setString(5, boniEnc.getTotal());
			stmt.setString(6, boniEnc.getBoni());
			stmt.setString(7, boniEnc.getRutRet());
			stmt.setString(8, boniEnc.getNomRet());
			stmt.setString(9, boniEnc.getFonoRet());
			stmt.setString(10, boniEnc.getObs());

			stmt.registerOutParameter(11, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(11);
			while (rs.next()) {
				//System.out.println("Se ingresa Boni X2 prod. Nume:"+rs.getString(1));
				BonificacionBoletaDTO boni = new BonificacionBoletaDTO();
				boni.setKdoc(rs.getString(1));
				listRetorno.add(boni);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error(horaActual+"Error FarBonificacionDAO.setBonificaEnc SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}

	public  List<BonificacionBoletaDTO> setBonificaDet(BonificaEncVO boniEnc) throws SQLException, IOException{

		Connection conn = ConexionPool.getConnectionPool();	

		List<BonificacionBoletaDTO> listRetorno = new ArrayList<BonificacionBoletaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.setBonificaDet(?,?,?,?,?, ?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, boniEnc.getNume());
			stmt.setString(2, boniEnc.getCorr());
			stmt.setString(3, boniEnc.getKdoc());
			stmt.setString(4, boniEnc.getKinf());
			stmt.setString(5, boniEnc.getLine());
			stmt.setString(6, boniEnc.getTotal());

			stmt.registerOutParameter(7, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(7);
			while (rs.next()) {
				BonificacionBoletaDTO boni = new BonificacionBoletaDTO();
				boni.setKdoc(rs.getString(1));
				boni.setCantBoni(rs.getString(2));
				listRetorno.add(boni);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error(horaActual+"Error FarBonificacionDAO.setBonificaDet SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {			
			conn.close();
			conn = null;
		}
		return listRetorno;
	}
	
	public List<ComboDTO> getMedicamento(String idPar) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.getMedicamento(?, ?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, idPar);				
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO cmb = new ComboDTO();
					cmb.setId(rs.getString(1));
					cmb.setDesc(rs.getString(2));
					listUserPerf.add(cmb);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error FarBonificacionDAO.getMedicamento SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}

	public List<BonificacionDTO> getBonificacion(String idKsuc, String fini, String ffin) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<BonificacionDTO> listUserPerf = new ArrayList<BonificacionDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_FAR_BONIFICACION.getBonificacion(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, idKsuc);
				stmt.setString(2, fini);
				stmt.setString(3, ffin);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					BonificacionDTO cmb = new BonificacionDTO();
					cmb.setNume(rs.getString(1));
					cmb.setFechaBcr(rs.getString(2));
					cmb.setPaciente(rs.getString(3));
					cmb.setMedicamento(rs.getString(4));
					cmb.setKdoc(rs.getString(5));
					cmb.setUser(rs.getString(6));
					cmb.setTipo(rs.getString(7));
					cmb.setFechaGuia(rs.getString(8));
					cmb.setCantBoni(rs.getString(9));
					listUserPerf.add(cmb);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error FarBonificacionDAO.getBonificacion SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}

}
